import {
    isArray,
    isObject,
    isString,
    OrderBy,
    PageRequest,
    PageResponse,
    Sql,
    TableConfig,
    ValidationError
} from "./common";
import {DbClient, Transaction, TxFun} from "./db/dbClient";
import {pgp} from "./db/postgresql";
import {getProperty, setProperty} from "./util/array";

// 配置选项接口
export interface DynamicQueryOptions {
    enableSqlLog?: boolean;
}

// 查询条件类型
type QueryCondition = {
    [key: string]: any;
    $or?: QueryCondition[];
    $and?: QueryCondition[];
};

// SQL操作符映射
const OPERATOR_MAP: Record<string, string> = {
    $nin: 'NOT IN',
    $in: 'IN',
    $eq: '=',
    $ne: '!=',
    $gte: '>=',
    $gt: '>',
    $lte: '<=',
    $lt: '<',
    $between: 'BETWEEN'
};

const OrAndMap = {
    $or: 'or',
    $and: 'and'
};

/**
 * 动态查询类，用于构建和执行数据库查询
 */
export class DynamicQuery {
    protected tableConfig: TableConfig;
    private client: DbClient;
    protected options: DynamicQueryOptions;

    constructor(tableConfig: TableConfig, client: DbClient, options: DynamicQueryOptions = {}) {
        this.tableConfig = tableConfig;
        this.client = client;
        this.options = {enableSqlLog: false, ...options};
    }

    /**
     * 记录SQL日志
     */
    private logSql(sql: string, ...prefix: string[]): void {
        if (this.options.enableSqlLog) {
            const colors = require('colors-console');
            if (prefix.length) {
                console.log(...prefix.map(value => colors('red', value)));
            }
            console.log(colors('cyan', sql));
        }
    }

    /**
     * 检查是否是父对象
     */
    private isParentObject(key: string): boolean {
        return this.tableConfig.parents.some(value => value.parentObject === key);
    }

    /**
     * 处理数组值
     */
    private arrayDataValueProcess(queryVal: any[]): any[] {
        if (isString(queryVal[0])) {
            return queryVal.map(i => `'${i}'`).filter(Boolean);
        }
        return queryVal;
    }

    /**
     * 获取数组条件
     */
    private getArrayConditions(queryVal: QueryCondition[], tableAlias: string): string[] {
        return queryVal.map(i => {
            const conditions = this.getConditions(i, tableAlias);
            return conditions.length > 0 ? `(${conditions})` : null;
        }).filter(Boolean) as string[];
    }

    /**
     * 处理特殊操作符
     */
    private specOperatorProcess(queryVal: Record<string, any>, column: string): string[] {
        const temp: string[] = [];

        for (const [operator, value] of Object.entries(queryVal)) {
            if (!OPERATOR_MAP[operator]) continue;

            let tempSql = '';
            const sqlOperator = OPERATOR_MAP[operator];

            switch (operator) {
                case '$nin':
                case '$in':
                    const processedValue = this.arrayDataValueProcess(value);
                    if (processedValue.length === 0) continue;
                    tempSql = ` ${column} ${sqlOperator} (${processedValue.join(',')})`;
                    break;
                case '$between':
                    if (!isArray(value)) {
                        throw new ValidationError("$between值必须为数组");
                    }
                    tempSql = ` ${column} ${sqlOperator} ${value[0]} AND ${value[1]}`;
                    break;
                default:
                    tempSql = ` ${column} ${sqlOperator} ${value}`;
            }

            if (tempSql) {
                temp.push(tempSql);
            }
        }

        return temp;
    }

    /**
     * 传入query对象,和表别名(可不传),返回一个转化后的sql条件数组
     */
    private getConditions(query: QueryCondition, tableAlias = this.tableConfig.table): string[] {
        const conditions: string[] = [];

        for (const [key, queryVal] of Object.entries(query)) {
            let column = this.client.columnProcess(key);
            if (tableAlias) {
                column = `${tableAlias}.${column}`;
            }

            if (isArray(queryVal)) {
                if (key === '$or' || key === '$and') {
                    const arrayConditions = this.getArrayConditions(queryVal, tableAlias);
                    if (arrayConditions.length > 0) {
                        conditions.push(arrayConditions.join(` ${OrAndMap[key as keyof typeof OrAndMap]} `));
                    }
                } else {
                    const processedValue = this.arrayDataValueProcess(queryVal);
                    if (processedValue.length === 0) {
                        throw new ValidationError("数组值不能为空!");
                    }
                    conditions.push(pgp.as.format(` ${column} IN (${processedValue.join(',')})`));
                }
            } else if (isObject(queryVal)) {
                if (Object.keys(OrAndMap).includes(key)) {
                    const nestedConditions = this.getConditions(queryVal, tableAlias);
                    if (nestedConditions.length > 0) {
                        conditions.push(nestedConditions.join(` ${OrAndMap[key as keyof typeof OrAndMap]} `));
                    }
                } else if (this.isParentObject(key)) {
                    conditions.push(...this.getConditions(queryVal, key));
                } else {
                    conditions.push(...this.specOperatorProcess(queryVal, column));
                }
            } else {
                // 处理基本值
                if (isString(queryVal) && queryVal.includes("%")) {
                    conditions.push(` ${column} LIKE '${queryVal}'`);
                } else if (queryVal === '$null') {
                    conditions.push(` ${column} IS NULL`);
                } else if (queryVal === '$nn') {
                    conditions.push(` ${column} IS NOT NULL`);
                } else if (queryVal !== null && queryVal !== undefined) {
                    conditions.push(pgp.as.format(` ${column} = $1`, [queryVal]));
                }
            }
        }

        return conditions;
    }

    /**
     * 传入query对象,和表别名(可不传),返回where sql 语句
     */
    private getWhere(query: QueryCondition, tableAlias = this.tableConfig.table): string {
        const conditions = this.getConditions(query, tableAlias);
        return conditions.length > 0 ? ` WHERE ${conditions.join(" AND ")}` : '';
    }

    /**
     * 传入OrderBy对象,返回order by sql 语句
     */
    private static getOrderBy(page?: PageResponse<any> | PageRequest | OrderBy): string {
        if (!page?.orderBy) return '';

        let orderSql = ` ORDER BY ${page.orderBy}`;
        if (page?.direction) {
            orderSql += ` ${page.direction}`;
        }
        return orderSql;
    }

    /**
     * 单表分页查询方法,数据会在PageResponse.content属性中
     */
    async find(query: QueryCondition, page: PageResponse<any>, transaction?: Transaction): Promise<void> {
        const where = this.getWhere(query);
        const orderBySql = DynamicQuery.getOrderBy(page);

        await this.findBySql({
            countInSql: null,
            selectSql: "SELECT * ",
            formWhereSql: ` FROM ${this.tableConfig.table}${where} `,
            orderBySql
        }, page, transaction);
    }

    /**
     * 根据tableConfig,获取from sql
     */
    private getFromSql(): string {
        const table = this.tableConfig.table;
        let fromSql = ` FROM ${table}`;

        for (const {parentId, parentObject, parentTable, parentIdName} of this.tableConfig.parents) {
            fromSql += `\n INNER JOIN ${parentTable} AS ${parentObject} ON ${table}.${parentId} = ${parentObject}.${parentIdName}`;
        }

        return `${fromSql}\n`;
    }

    /**
     * 导航分页查询方法,前提需在TableConfig配置parents信息
     */
    async navigationFind(query: QueryCondition = {}, page: PageResponse<any>, transaction?: Transaction): Promise<void> {
        const where = this.getWhere(query);
        const orderBySql = DynamicQuery.getOrderBy(page);

        await this.findBySql({
            countInSql: null,
            selectSql: `SELECT ${this.tableConfig.table}.*`,
            formWhereSql: ` ${this.getFromSql()}${where}`,
            orderBySql
        }, page, transaction);

        await this.fillParentObject(page.content, transaction);
    }

    /**
     * 通过Sql对象分页查询
     */
    async findBySql({
                        selectSql,
                        formWhereSql,
                        countInSql,
                        orderBySql = ""
                    }: Sql, page: PageResponse<any>, transaction?: Transaction): Promise<void> {
        const countSql = `SELECT ${countInSql ?? "COUNT(1) AS count"}${formWhereSql}`;

        this.logSql(countSql, 'findBySql', 'countSql');
        const {count} = await this.client.one(countSql, transaction);

        if (count > 0) {
            const offset = page.page * page.size;
            const sql = `${selectSql}\n${formWhereSql}\n${orderBySql} LIMIT ${page.size} OFFSET ${offset}`;

            this.logSql(sql, 'findBySql', 'findSql');
            page.content = await this.client.query(sql, transaction) || [];
            this.returnHook(page.content);
        }

        page.setTotalElements(parseInt(count));
    }

    /**
     * 通过自定义SQL分页查询
     */
    async pageBySql(countSql: string, selectSql: string, page: PageResponse<any>, transaction?: Transaction): Promise<void> {
        this.logSql(countSql, 'pageBySql', 'countSql');
        const {count} = await this.client.one(countSql, transaction);

        if (count > 0) {
            const offset = page.page * page.size;
            const sql = `${selectSql} LIMIT ${page.size} OFFSET ${offset}`;

            this.logSql(sql, 'pageBySql', 'findSql');
            page.content = await this.client.query(sql, transaction) || [];
        }

        page.setTotalElements(parseInt(count));
    }

    /**
     * 通过sql查询
     */
    async findAllBySql(querySql: string, transaction?: Transaction): Promise<any[]> {
        this.logSql(querySql, 'findAllBySql');
        return await this.client.query(querySql, transaction) || [];
    }

    /**
     * 事务方法,func中的所有数据库操作都会在一个事务中
     */
    async tx(func: TxFun): Promise<any> {
        return await this.client.tx(func);
    }

    /**
     * 传入Query对象,和OrderBy,返回所有符合条件记录
     */
    async findAll(query: QueryCondition = {}, transaction?: Transaction, page: OrderBy = {}): Promise<any[]> {
        const orderBySql = DynamicQuery.getOrderBy(page);
        const where = this.getWhere(query);
        const sql = `SELECT *
                     FROM ${this.tableConfig.table} ${where}${orderBySql}`;

        const list = await this.findAllBySql(sql, transaction);
        this.returnHook(list);
        return list;
    }

    /**
     * 导航查询全部
     */
    async navigationFindAll(query: QueryCondition = {}, page?: OrderBy, transaction?: Transaction): Promise<any[]> {
        const orderBySql = DynamicQuery.getOrderBy(page);
        const where = this.getWhere(query);
        const r = await this.findAllBySql(
            `SELECT ${this.tableConfig.table}.*${this.getFromSql()}${where}${orderBySql}`,
            transaction
        );

        this.returnHook(r);
        await this.fillParentObject(r, transaction);
        return r;
    }

    /**
     * 导航查询,返回第一个
     */
    async navigationFindOne(query: QueryCondition = {}, transaction?: Transaction): Promise<any> {
        const where = this.getWhere(query);
        const r = await this.findOneBySql(
            `SELECT ${this.tableConfig.table}.*${this.getFromSql()}${where}`,
            transaction
        );

        if (r) {
            await this.fillParentObject([r], transaction);
        }

        this.returnHook(r);
        return r;
    }

    /**
     * 传入Query对象,返回第一个符合的对象
     */
    async findOne(query: QueryCondition = {}, transaction?: Transaction): Promise<any> {
        const where = this.getWhere(query);
        const sql = `SELECT *
                     FROM ${this.tableConfig.table} ${where}`;

        const r = await this.findOneBySql(sql, transaction);
        this.returnHook(r);
        return r;
    }

    /**
     * 传入sql,返回第一个记录
     */
    async findOneBySql(sql: string, transaction?: Transaction): Promise<any> {
        const finalSql = `${sql} LIMIT 1`;
        this.logSql(finalSql, 'findOneBySql');
        return await this.client.oneOrNone(finalSql, transaction);
    }

    /**
     * 传入Query,返回符合条件的数目
     */
    async count(query: QueryCondition = {}, transaction?: Transaction): Promise<number> {
        const where = this.getWhere(query);
        return await this.countBySql(`${this.getFromSql()}${where}`, transaction);
    }

    /**
     * 传入from where sql,返回符合数目
     */
    async countBySql(sql: string, transaction?: Transaction): Promise<number> {
        const finalSql = `SELECT COUNT(*) AS count${sql} LIMIT 1`;
        this.logSql(finalSql, 'countBySql');
        const r = await this.client.oneOrNone(finalSql, transaction);
        return r ? r.count : 0;
    }

    /**
     * 创建新记录,hasReturn是否返回创建的记录,默认返回
     */
    async create(data: Record<string, any> = {}, hasReturn = true, transaction?: Transaction): Promise<any> {
        const cs = this.prepareCreate(data);
        const sql = this.client.insertSql(data, cs, this.tableConfig);

        this.logSql(sql, 'create');

        if (hasReturn && this.tableConfig.idAutoCreate) {
            const res = await this.client.one(sql, transaction);
            // MySQL res.insertId
            const id = res[this.tableConfig.idName] || res.insertId;
            data[this.tableConfig.idName] = id;
            return await this.findById(id, transaction);
        } else {
            return await this.client.none(sql, transaction);
        }
    }

    /**
     * 批量创建记录
     */
    async createAll(entities: Record<string, any>[], transaction?: Transaction): Promise<void> {
        for (const entity of entities) {
            await this.create(entity, this.tableConfig.createReturn, transaction);
        }
    }

    /**
     * 删除并创建新记录
     */
    async removeAndCreate(removeQuery: QueryCondition, entities: Record<string, any>[], transaction?: Transaction): Promise<void> {
        await this.removeByQuery(removeQuery, transaction);
        await this.createAll(entities, transaction);
    }

    /**
     * 保存方法,有则更新,无则创建,会返回结果
     */
    async save(data: Record<string, any> = {}, transaction?: Transaction): Promise<any> {
        const id = data[this.tableConfig.idName];
        let exist;

        if (this.tableConfig.idAutoCreate) {
            exist = id;
        } else {
            exist = await this.isExist(id, transaction);
        }

        if (exist) {
            return await this.update(data, false, transaction);
        } else {
            return await this.create(data, this.tableConfig.createReturn, transaction);
        }
    }

    /**
     * 批量保存
     */
    async saveAll(entities: Record<string, any>[], transaction?: Transaction): Promise<any[]> {
        const result = [];
        for (const entity of entities) {
            result.push(await this.save(entity, transaction));
        }
        return result;
    }

    /**
     * 检查记录是否存在
     */
    async isExist(id: any, transaction?: Transaction): Promise<boolean> {
        if (!id) {
            return false;
        }
        return await this.isExistByQuery({[this.tableConfig.idName]: id}, transaction);
    }

    /**
     * 根据查询条件检查记录是否存在
     */
    async isExistByQuery(query: QueryCondition, transaction?: Transaction): Promise<boolean> {
        const count = await this.count(query, transaction);
        return count > 0;
    }

    /**
     * 更新方法,isAllUpdate为true为全部更新,false为部分更新,默认为true,会返回结果
     */
    async update(data: Record<string, any>, isAllUpdate = true, transaction?: Transaction): Promise<any> {
        const cs = this.prepareUpdate(data, isAllUpdate);
        const idConditions = this.getIdCondition(data);
        const updateSql = this.client.updateSql(data, cs, this.tableConfig);
        const sql = `${updateSql} WHERE ${idConditions}`;

        this.logSql(sql, 'update');
        await this.client.none(sql, transaction);
        return await this.findById(data[this.tableConfig.idName], transaction);
    }

    /**
     * 根据ids更新,会返回更新结果集
     */
    async updateByIds(data: Record<string, any>, ids: any[], isAllUpdate = true, transaction?: Transaction): Promise<any[]> {
        const cs = this.prepareUpdate(data, isAllUpdate);
        const whereSql = ` WHERE ${this.tableConfig.idName} IN (${this.idsProcess(ids)})`;
        const sql = `${this.client.updateSql(data, cs, this.tableConfig)}${whereSql}`;

        this.logSql(sql, 'updateByIds');
        await this.client.none(sql, transaction);
        return await this.findByIds(ids, transaction);
    }

    /**
     * 根据Query更新数据,不会返回结果集
     */
    async updateByQuery(data: Record<string, any>, query: QueryCondition, isAllUpdate = true, transaction?: Transaction): Promise<void> {
        const cs = this.prepareUpdate(data, isAllUpdate);
        const where = this.getWhere(query);
        const sql = `${this.client.updateSql(data, cs, this.tableConfig)}${where}`;

        this.logSql(sql, 'updateByQuery');
        await this.client.none(sql, transaction);
    }

    /**
     * 根据Query更新数据,返回结果集
     */
    async updateByQueryWithResult(data: Record<string, any>, query: QueryCondition, isAllUpdate = true, transaction?: Transaction): Promise<any[]> {
        const entities = await this.findAll(query, transaction);
        const ids = entities.map(i => i[this.tableConfig.idName]);

        if (ids.length === 0) {
            return [];
        }

        return await this.updateByIds(data, ids, isAllUpdate, transaction);
    }

    /**
     * 准备更新数据
     */
    private prepareUpdate(data: Record<string, any>, isAllUpdate = true): any {
        let columnSet;
        if (!isAllUpdate) {
            columnSet = Object.keys(data).filter(i => this.tableConfig.columnSet.includes(i));
        } else {
            columnSet = [...this.tableConfig.columnSet];
        }
        return this.prepare(data, columnSet);
    }

    /**
     * 准备创建数据
     */
    private prepareCreate(data: Record<string, any>): any {
        let columnSet;
        if (this.tableConfig.idAutoCreate) {
            columnSet = this.tableConfig.columnSet.filter(value => value !== this.tableConfig.idName);
        } else {
            columnSet = [...this.tableConfig.columnSet];
        }
        return this.prepare(data, columnSet);
    }

    /**
     * 获取ID条件
     */
    private getIdCondition(data: Record<string, any>): string {
        return this.getConditions({
            [this.tableConfig.idName]: data[this.tableConfig.idName]
        })[0];
    }

    /**
     * 处理JSON字段
     */
    private jsonProcess(data: Record<string, any>): void {
        for (const key of Object.keys(data)) {
            if (this.tableConfig.jsonColumn.includes(key)) {
                data[key] = JSON.stringify(data[key]);
            }
        }
    }

    /**
     * 处理时间字段
     */
    private timeProcess(data: Record<string, any>, columnSet: string[]): void {
        const createTime = this.tableConfig.createTime;
        if (createTime && !data[createTime]) {
            if (!columnSet.includes(createTime)) {
                columnSet.push(createTime);
            }
            data[createTime] = Date.now();
        }

        const updateTime = this.tableConfig.updateTime;
        if (updateTime) {
            if (!columnSet.includes(updateTime)) {
                columnSet.push(updateTime);
            }
            data[updateTime] = Date.now();
        }
    }

    /**
     * 准备数据
     */
    private prepare(data: Record<string, any>, columnSet: string[] = []): any {
        this.initProperty(data);
        this.jsonProcess(data);
        this.timeProcess(data, columnSet);
        return new pgp.helpers.ColumnSet(columnSet, {table: this.tableConfig.table});
    }

    /**
     * 初始化属性
     */
    private initProperty(data: Record<string, any>): void {
        this.tableConfig.columnSet.forEach(c => {
            if (data[c] === undefined) {
                data[c] = null;
            }
        });
    }

    /**
     * 根据id查询
     */
    async findById(id: any, transaction?: Transaction): Promise<any> {
        const idName = this.tableConfig.idName;
        const sql = `SELECT *
                     FROM ${this.tableConfig.table}
                     WHERE ${idName} = ${id}`;

        this.logSql(sql, 'findById');
        const r = await this.client.oneOrNone(sql, transaction);
        this.returnHook(r);
        return r;
    }

    /**
     * 处理ID数组
     */
    private idsProcess(ids: any[]): string {
        return isString(ids[0])
            ? ids.map(i => `'${i}'`).join(',')
            : ids.join(',');
    }

    /**
     * 返回钩子
     */
    private returnHook(result: any): void {
        this.client.returnJsonProcess(result, this.tableConfig);
    }

    /**
     * 根据ids查询
     */
    async findByIds(ids: any[], transaction?: Transaction): Promise<any[]> {
        if (ids.length === 0) {
            return [];
        }

        const idsStr = this.idsProcess(ids);
        const sql = `SELECT *
                     FROM ${this.tableConfig.table}
                     WHERE ${this.tableConfig.idName} IN (${idsStr})`;

        this.logSql(sql, 'findByIds');
        const list = await this.client.query(sql, transaction);
        this.returnHook(list);
        return list;
    }

    /**
     * 根据ids删除
     */
    async remove(ids: any[] = [], transaction?: Transaction): Promise<void> {
        if (ids.length === 0) {
            return;
        }

        const sql = `DELETE
                     FROM ${this.tableConfig.table}
                     WHERE ${this.tableConfig.idName} IN (${this.idsProcess(ids)})`;

        this.logSql(sql, 'remove');
        await this.client.none(sql, transaction);
    }

    /**
     * 根据Query查询,删除相应记录
     */
    async removeByQuery(query: QueryCondition = {}, transaction?: Transaction): Promise<void> {
        const where = this.getWhere(query);
        const sql = `DELETE
                     FROM ${this.tableConfig.table} ${where}`;

        this.logSql(sql, 'removeByQuery');
        await this.client.none(sql, transaction);
    }

    /**
     * 填充父对象
     */
    protected async fillParentObject(content: any[], transaction?: Transaction): Promise<void> {
        if (!content?.length) {
            return;
        }

        for (const {parentId, parentObject, parentTable, parentIdName} of this.tableConfig.parents) {
            const parentIds = getProperty(content, parentId);
            const parentData = await this.findAllBySql(
                `SELECT *
                 FROM ${parentTable}
                 WHERE ${parentIdName} IN (${this.idsProcess(parentIds)})`,
                transaction
            );

            setProperty(
                content,
                parentData,
                (dbEntity, entity) => dbEntity[parentIdName] === entity[parentId],
                (dbEntity, entity) => {
                    entity[parentObject] = dbEntity;
                }
            );
        }
    }
}

/**
 * 设置是否打印sql
 */
export function switchSqlLog(b: boolean): void {
    // 这个函数可以保留，但现在应该通过构造函数选项来设置
    console.warn('switchSqlLog is deprecated. Use DynamicQuery constructor options instead.');
}